package DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {
	private final String server = "localhost";
	private final String username = "root";
	private final String password = "";
	
    private static Connection conn;
    
    /**
     * Laad de database klasse in
     *
     */
    public Database() {
    	try {
			Class.forName("com.mysql.jdbc.Driver");
			
			conn = DriverManager.getConnection( "jdbc:mysql://" + server + ":3306", username, password);
			
			// Maak db-structuur als die er niet zijn
			createStructure();
		}
		catch(Exception e ) {
			System.out.println( "Error while connecting to the database: " + e);
		}
    }
    
    /**
     * Maakt de structuur van de database ( tabellen e.d)
     *
     */
    public void createStructure(){
    	try {
	    	Statement stmt = conn.createStatement();
	    	
	    	// Probeer database te maken
    	   	try {
	    		stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS toweldefense");
    	   	} catch ( Exception e ) {}
	    	
	    	// Proberen tabel spel te maken
    	   	try {
	    		stmt.executeUpdate("CREATE TABLE toweldefense.spel " +
		        		"( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
		        		" naam VARCHAR(50) ," +
		        		" email VARCHAR(50) ," +
		        		" map INTEGER ," +
		        		" score INTEGER )");
	    	} catch ( Exception e ) {}
	       
	    	//proberen tabel score te maken
	        try {
	        	stmt.executeUpdate("CREATE TABLE toweldefense.score " +
	        		"( id INTEGER NOT NULL, " +
	        		" lvl INTEGER ," +
	        		" tijd INTEGER ," +
	        		" aantalTorens INTEGER ," +
	        		" basisHealth INTEGER ," +
	        		" scorePunten INTEGER ," +
	        		" geld INTEGER ," +
	        		" kills INTEGER )");
	        } catch( Exception e ){}
	        
	        stmt.close();
    	}
		catch(Exception e ) {
			System.out.println( e);
		}
    }
    
    /**
     * Haal de beste 10 scores uit de DB
     * @param map
     * @return Array van highscores
     */
    public String[][] getBest( int map ){
    	String arr[][] = new String[10][2];
    	
    	if ( conn != null ) {
	    	try {
	    		Statement stmt = conn.createStatement();
	    		ResultSet results = stmt.executeQuery( "SELECT naam, score FROM toweldefense.spel " + 
	    				"WHERE map = " + map + " ORDER BY score DESC" );
	    		int i = 0;
	    		while(results.next() && i < 10)
	            {
		    		if ( results.getString(1) != null ) {
		    			arr[i][0] = "" + results.getString(1);
		        		arr[i][1] = "" + results.getInt(2);
		        		i++;
	    			}
	            }
	    		
	    		results.close();
	    		stmt.close();
	    		
	        	return arr;
	    	} catch ( Exception e ){
	    		System.out.println( "Error while fetching data best player" );
	    	}
    	}
    	
    	return arr;
    }
    
    /**
     * Haal de highscore van een bepaalde map op
     * @param map
     * @return De beste score van de meegegeven map
     */
    public int getBestScore( int map ){
    	try {
    		Statement stmt = conn.createStatement();
    		ResultSet results = stmt.executeQuery( "SELECT score FROM toweldefense.spel " + 
    				"WHERE map = " + map + " AND score IN ( " + 
    				"SELECT max( score ) FROM toweldefense.spel GROUP BY map )" );
    		int retVal =0;
    		while(results.next())
            {
        		retVal = results.getInt(1);
            }
    		
    		results.close();
    		stmt.close();
    		
        	return retVal;
    	} catch ( Exception e ){
    		System.out.println( "Error while fetching data best player" );
    		return 0;	
    	}
    	
    }
    
    /**
     * Voegt een nieuwe rij in de tabel spel toe
     * @param naam
     * @param email
     * @param map
     * @param score
     */
    public void insertIntoSpel( String naam, String email , int map, int score, int moeilijkheid ) {
        if ( conn != null ) {
	    	try
	        {
	            Statement stmt = conn.createStatement();
	            stmt.execute("INSERT INTO toweldefense.spel (naam, email, map, score, moeilijkheid ) " + 
	            		"VALUES ( '" + naam + "','" + email + "'," + map + "," + score + "," + moeilijkheid + ")");
	            stmt.close();
	        }
	        catch (SQLException sqlExcept)
	        {
	            System.out.println( "An error has occured while inserting into spel" );
	        }
        }
    }
    
    public void insertIntoSpel( String naam, String email , int map, int score ) {
    	this.insertIntoSpel(naam, email, map, score,2);
    }
    
    /**
     * Voegt een nieuwe rij in de tabel score toe
     * @param level
     * @param tijd
     * @param aantalTorens
     * @param basisHealth
     * @param scorePunten
     * @param geld
     * @param kills
     */    
    public void insertIntoScore( int level, int tijd, int aantalTorens, int basisHealth, int scorePunten, int geld, int kills ){
    	if ( conn != null ) {
	    	try{
	    		Statement stmt = conn.createStatement();
	    		// maken dat hij automatisch het id fetcht
	    		int id = 0;
	    		ResultSet results = stmt.executeQuery( "SELECT max(id) FROM toweldefense.spel" );
	    		while(results.next())
	            {
	                id = results.getInt(1);
	            }
	            stmt.execute("INSERT INTO toweldefense.score ( id, lvl, tijd, aantalTorens, basisHealth, scorePunten, geld, kills ) " + 
	            		"VALUES ( " + id + ", " + level + "," + tijd + "," + aantalTorens + "," + basisHealth + "," + scorePunten + "," + geld + "," + kills + ")");
	            stmt.close();
	    	} catch ( Exception e ){
	    		System.out.println( "An error has occured while inserting into scores" );
	    	}
    	}
    }
    
    /**
     * Verwijdert uit de score tabel 
     *
     */
    public void removeAllFromScore() {
    	if ( conn != null ) {
	    	try{
	    		Statement stmt = conn.createStatement();
	    		stmt.execute( "DELETE FROM toweldefense.score" );
	    		stmt.close();
	    	} catch ( Exception e ){
	    		System.out.println( "An error has occured deleting rows from score" );
	    	}
    	}
    }
    
    /**
     * Sluit de database
     *
     */
    public void close() {
        try {
            if (conn != null) {
                conn.close();
            }
        }
        catch (SQLException e){}
    }
    
    /**
     * krijg de hoogste score van een user/email combinatie behorend bij een map en moelijkheidsgraad
     * @param username
     * @param email
     * @param map
     * @param moeilijkheid
     * @return
     */
    public int getUserHighScore(String username, String email, int map, int moeilijkheid){
    	if ( conn != null ) {
	    	try{
	    		Statement stmt = conn.createStatement();
	    		ResultSet results = stmt.executeQuery( 	"SELECT score FROM toweldefense.spel" +
	    						" WHERE naam='"+username+"' " +
	    						" AND email='"+email+"'" +
	    						" AND moeilijkheid="+moeilijkheid+"" +
	    						" AND map="+map+ 
	    						" ORDER BY score DESC" +
	    						" LIMIT 1");
	    		int score = 0;
	    		while(results.next())
	            {
	                score = results.getInt(1);
	            }
	    		stmt.close();
	    		return score;
	    	} catch ( Exception e ){
	    		System.out.println(e);
	    		System.out.println( "An error has occured selecting rows from score" );
	    		System.out.println( 	"SELECT score FROM toweldefense.spel" +
						" WHERE naam='"+username+"' " +
						" AND email='"+email+"'" +
						" AND moeilijkheid="+moeilijkheid+"" +
						" AND map="+map+ 
						" ORDER BY score DESC" +
						" LIMIT 1");
	    	}
    	}
    	return 0;
    }
    
}